﻿import time
import pymysql


"""
    用于查询数据库里面的数据
"""


def get_time():
    str_time = time.strftime("%Y{}%m{}%d{} %X")  # 获取当前时间转化为字符 串

    return str_time.format("年", "月", "日")


# print(get_time())

def get_conn():
    """
    return:连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host="localhost", user="root", password="mysql", db="cov", charset="utf8")

    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor


def close_conn(conn, cursor):  # 关闭
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def query(sql, *args):  # 查询
    '''
    封装通用查询
    :param sql:
    :param args:
    :return:  返回查询到的结果,((),(),)的形式
    '''
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_cl_data():
    """

    :return:返回大屏div id = c1的数据
    """
    # 因为会更新多次数据，即时间戳最新的那组数据
    sql = "select sum(confirm)," \
          "(select suspect from history order by ds desc limit 1)," \
          "sum(heal)," \
          "sum(dead) " \
          "from details " \
          "where update_time=(select update_time from details order by update_time desc limit 1);"

    res = query(sql)
# select sum(confirm),(select suspect from history order by ds desc limit 1),sum(heal),sum(dead) from details where update_time=(select update_time from details order by update_time desc limit 1);
    return res[0]


# 查询语句  (Decimal('85204'), 4, Decimal('80044'), Decimal('4648'))
# select sum(confirm),(select suspect from history order by ds desc limit 1),sum(heal),sum(dead) from details where update_time=(select update_time from details order by update_time desc limit 1);

def get_c2_data():
    """
    :return:  返回各省数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,sum(confirm) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res


def get_l1_data():
    sql = "select ds,confirm,suspect,heal,dead from history;"
    res = query(sql)
    return res


def get_l2_data():
    sql = "select ds,confirm,suspect from history"
    res = query(sql)
    return res


def get_r1_data():
    """
    :return:  返回非湖北地区城市确诊人数前5名
    """
    sql = 'SELECT city,confirm FROM ' \
          '(select city,confirm from details  ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province not in ("湖北","北京","上海","天津","重庆") ' \
          'union all ' \
          'select province as city,sum(confirm) as confirm from details  ' \
          'where update_time=(select update_time from details order by update_time desc limit 1) ' \
          'and province in ("北京","上海","天津","重庆") group by province) as a ' \
          'ORDER BY confirm DESC LIMIT 5'
    res = query(sql)
    return res


def get_r2_data():
    """
    :return:  返回最近的20条热搜
    """
    sql = 'select content from hotsearch order by id desc limit 20'
    res = query(sql)  # 格式 (('民警抗疫一线奋战16天牺牲1037364',), ('四川再派两批医疗队1537382',)
    return res


if __name__ == '__main__':
    get_cl_data()
    get_c2_data()
    get_l1_data()
    get_l2_data()
    get_r1_data()
    get_r2_data()


